<?php
declare (strict_types = 1);

namespace app\command\logic;

use app\command\logic\BaseLogic;
use think\console\Output;

/**
 * 统计-会员数据
 * php think cli stats_member --msg='{"days":30}'
 */
class CliStatsMemberLogic extends BaseLogic implements CliInterface
{

    /**
     * 入口方法
     * {@inheritdoc}
     */
    public function run(Output $output, array $msg): bool
    {
        $insertData = [];
        $date       = isset($msg['date']) ? ($msg['date'] == 'today' ? date('Y-m-d') : $msg['date']) : null;
        if ( ! empty($date)) {
            self::dataForMemberConsumed($date);
            self::dataForMemberArea($date);
            $insertData[] = self::dataForMember($date);
        } else {
            $days = intval($msg['days'] ?? 1);
            for ($i = 1; $i <= $days; $i++) {
                $date = date('Y-m-d', strtotime("-$i days"));

                self::dataForMemberConsumed($date);
                self::dataForMemberArea($date);
                $insertData[] = self::dataForMember($date);
            }
        }
        if ( ! empty($insertData)) {
            \think\facade\Db::table('stats_member')
                ->replace()
                ->insertAll($insertData);
        }
        return true;
    }
    private static function dataForMember($date)
    {
        $end = $date . ' 23:59:59';

        // agency_type 1省代 2市代 3区代 4 区代
        $field = [
            'COALESCE(SUM(CASE WHEN agency_type IN (3, 4) THEN 1 ELSE 0 END), 0) AS agency_3_count',
            'COALESCE(SUM(CASE WHEN agency_type = 2 THEN 1 ELSE 0 END), 0) AS agency_2_count',
            'COALESCE(SUM(CASE WHEN agency_type = 1 THEN 1 ELSE 0 END), 0) AS agency_1_count',
        ];
        $data = \think\facade\Db::table('member_agency')
            ->field($field)
            ->where('deleted', 0)
        // 状态     1正常   0取消
            ->where('status', 1)
            ->whereBetweenTime('create_at', $date, $end)
            ->find();
        // echo \think\facade\Db::table('member_agency')->getlastsql();
        // var_dump($data);exit;
        $data['date'] = $date;

        $data2 = [
            'up_vip_count'     => 0,
            'up_partner_count' => 0,
        ];
        // give_member_level 赠送会员等级 空表示不赠送 vip 赠送VIP  partner 赠送服务商
        $field2 = [
            'member_id',
            'give_member_level',
        ];
        $items2 = \think\facade\Db::table('packs_order')
            ->field($field2)
            ->where('deleted', 0)
        // 状态     1正常   0取消
            ->where('status', 1)
            ->whereBetweenTime('create_at', $date, $end)
            ->select()
            ->toArray();
        if ( ! empty($items2)) {
            $up_vip_count     = [];
            $up_partner_count = [];
            foreach ($items2 as $row) {
                $member_id = $row['member_id'];
                if ($row['give_member_level'] == 'vip') {
                    $up_vip_count[$member_id] = 1;
                } elseif ($row['give_member_level'] == 'partner') {
                    $up_partner_count[$member_id] = 1;
                }
            }
            $data2['up_vip_count']     = count($up_vip_count);
            $data2['up_partner_count'] = count($up_partner_count);
        }
        // echo \think\facade\Db::table('packs_order')->getlastsql();
        // var_dump($data2);exit;

        // use index
        $data['order_member_count'] = \think\facade\Db::table('order')
            ->where('deleted', 0)
            ->whereBetweenTime('create_at', $date, $end)
            ->count('distinct user_id');
        // echo \think\facade\Db::table('order')->getlastsql();
        // var_dump($data['order_member_count']);exit;

        // 访问人数
        $data['view_count'] = \think\facade\Db::table('member_oplog')
            ->whereBetweenTime('create_at', $date, $end)
            ->count('distinct user_id');

        // use index
        $data['amount'] = \think\facade\Db::table('member')
            ->where('create_at', '<=', $end)
            ->count();
        $data['new_count'] = \think\facade\Db::table('member')
            ->whereBetweenTime('create_at', $date, $end)
            ->count();

        // echo \think\facade\Db::table('member_oplog')->getlastsql();exit;
        $data['signin_count'] = \think\facade\Db::table('community_sign_user')
        // ->where('type', 1) //状态 1社区签到
            ->whereBetweenTime('create_at', $date, $end)
            ->count();

        // 审核拒绝商家
        $data['shop_rjection_count'] = \think\facade\Db::table('system_oplog')
            ->where('action', '商家审核拒绝')
            ->whereBetweenTime('create_at', $date, $end)
            ->count();
        // echo \think\facade\Db::table('system_oplog')->getlastsql();exit;

        // 审核通过商家
        $data['shop_approved_count'] = \think\facade\Db::table('member_shop_apply')
            ->where('deleted', 0)
            ->where('status', 1) // 0 审核中 1 成功 2 拒绝
            ->whereBetweenTime('auth_at', $date, $end)
            ->count();

        // 申请商家
        $data['shop_apply_count'] = \think\facade\Db::table('member_shop_apply')
            ->where('deleted', 0)
            ->whereBetweenTime('create_at', $date, $end)
            ->count();

        return array_merge($data, $data2);
    }
    private static function dataForMemberArea($date)
    {
        $end = $date . ' 23:59:59';

        $field = [
            'id',
            'province_id',
            'city_id',
            'country_id',
            'create_at',
        ];
        $rows = \think\facade\Db::table('member')
            ->field($field)
            ->where('create_at', '<=', $end)
            ->select()
            ->toArray();
        if (empty($rows)) {
            return [];
        }
        $reg_total = [];
        $startT    = strtotime($date);
        $endT      = strtotime($end);
        foreach ($rows as $row) {
            $create_at = strtotime($row['create_at']);

            $isNew = $create_at >= $startT && $create_at <= $endT ? 1 : 0;
            $k     = $row['country_id'];
            if (isset($reg_total[$k])) {
                $reg_total[$k]['amount'] += 1;
                $reg_total[$k]['new_count'] += $isNew;
            } else {
                $name1 = \think\facade\Db::table('area')
                    ->where('this_id', $row['province_id'])
                    ->value('name');
                $name2 = \think\facade\Db::table('area')
                    ->where('this_id', $row['city_id'])
                    ->value('name');
                $name3 = \think\facade\Db::table('area')
                    ->where('this_id', $row['country_id'])
                    ->value('name');
                $reg_total[$k] = [
                    'amount'     => 1,
                    'area_ids'   => implode('_', [
                        $row['province_id'],
                        $row['city_id'],
                        $row['country_id'],
                    ]),
                    'area'       => implode(' ', [$name1, $name2, $name3]),
                    'new_count'  => $isNew,
                    'date'       => $date,
                    'view_count' => 0,
                ];
            }
        }
        $field = [
            'province',
            'city',
            'district',
            'adcode country_id',
            'count(distinct user_id) AS view_count',
        ];
        $rows = \think\facade\Db::table('member_reverse_geocoding')
            ->field($field)
            ->whereBetweenTime('create_at', $date, $end)
            ->group('adcode')
            ->select()->toArray();
        // echo json_encode($rows, 320);exit;
        if ( ! empty($rows)) {
            foreach ($rows as $row) {
                try {
                    $k = $row['country_id'];
                    if (isset($reg_total[$k])) {
                        $reg_total[$k]['view_count'] = $row['view_count'];
                    } else {
                        $city_id = $row['country_id'] > 0?\think\facade\Db::table('area')
                            ->where('this_id', $row['country_id'])
                            ->value('p_id') : 0;
                        $province_id = $city_id > 0?\think\facade\Db::table('area')
                            ->where('this_id', $city_id)
                            ->value('p_id') : 0;

                        $reg_total[$k] = [
                            'amount'     => 0,
                            'new_count'  => 0,
                            'area_ids'   => implode('_', [
                                $province_id,
                                $city_id,
                                $row['country_id'],
                            ]),
                            'area'       => implode(' ', [
                                $row['province'],
                                $row['city'],
                                $row['district'],
                            ]),
                            'date'       => $date,
                            'view_count' => $row['view_count'],
                        ];
                    }
                } catch (\Exception $e) {
                    var_dump($e->getMessage());
                    var_dump($e->getTrace());
                    exit;
                }
            }
        }

        // var_dump($reg_total);exit;
        foreach ($reg_total as $row) {
            \think\facade\Db::table('stats_member_area')
                ->replace()
                ->insert($row);
        }
        // echo json_encode($reg_total, 320);exit;
        // try {
        //     \think\facade\Db::table('stats_member_area')
        //         ->replace()
        //         ->insertAll($reg_total);
        // } catch (\Exception $e) {
        //     echo json_encode($e->getTrace(), 320);
        // }

        return true;
    }

    private static function dataForMemberConsumed($date)
    {
        $end = $date . ' 23:59:59';

        // status 0待支付 1已支付 2已取消
        $field = [
            'user_id',
            'COALESCE(SUM(CASE WHEN status = 1 THEN pay_price ELSE 0 END), 0) AS pay_price',
            'COALESCE(count(CASE WHEN status = 1 THEN 1 ELSE 0 END), 0) AS orde_count',
        ];
        $rows = \think\facade\Db::table('order_pay')
            ->field($field)
            ->where('deleted', 0)
            ->whereBetweenTime('create_at', $date, $end)
            ->group('user_id')
            ->select()->toArray();
        if (empty($rows)) {
            return true;
        }
        // echo \think\facade\Db::table('order_pay')->getlastsql();
        // var_dump($rows);exit;
        $user_ids = array_column($rows, 'user_id');
        $userMap  = \think\facade\Db::table('member')
            ->whereIn('id', $user_ids)
            ->column(['id', 'level', 'user_name'], 'id');
        // var_dump($userMap);exit;

        $insertData = [];
        foreach ($rows as $row) {
            $user             = $userMap[$row['user_id']] ?? [];
            $row['date']      = $date;
            $row['level']     = $user['level'] ?? 0;
            $row['user_name'] = $user['user_name'] ?? '';
            $insertData[]     = $row;
        }
        \think\facade\Db::table('stats_member_consumed')
            ->replace()
            ->insertAll($insertData);
        return true;
    }
}
